location
Table Name: location
The location table is designed to store geographical location data and support hierarchical relationships between locations. It is used to manage various types of locations such as cities, villages, districts, and other administrative or physical entities. The table not only stores basic details about each location (e.g., name, address, and geographical coordinates) but also supports parent-child relationships for nested locations (e.g., a city within a district). Additionally, it tracks the user who created or edited the location and handles the status of locations (e.g., active or voided).
Columns
| Column Name | Data Type | Description |
|---|---|---|
| locationId | int(11) | The unique identifier for the location. |
| name | varchar(45) | The short name or identifier of the location. |
| shortName | varchar(45) | A shorter version or abbreviation of the location name. |
| fullName | varchar(45) | The full, official name of the location. |
| parentLocation | int(11) | The locationId of the parent location if this is a sub-location. |
| description | varchar(255) | A description of the location. |
| otherIdentifier | varchar(45) | Any other identifier related to the location, such as a code or reference number. |
| locationType | int(11) | The type of location, referencing the locationtype table. |
| address1 | varchar(255) | The first line of the address. |
| address2 | varchar(255) | The second line of the address. |
| address3 | varchar(255) | The third line of the address, if applicable. |
| address4 | varchar(255) | The fourth line of the address, if applicable. |
| address5 | varchar(255) | The fifth line of the address, if applicable. |
| address6 | varchar(255) | The sixth line of the address, if applicable. |
| cityVillage | varchar(255) | The city or village the location is situated in. |
| country | varchar(255) | The country where the location is located. |
| countyDistrict | varchar(255) | The county or district the location is in. |
| createdDate | datetime | The date and time when the location was created. |
| lastEditedDate | datetime | The date and time when the location was last edited. |
| latitude | varchar(255) | The latitude of the location. |
| longitude | varchar(255) | The longitude of the location. |
| voided | tinyint(4) | Indicates whether the location has been voided (deleted or inactive). 0 is active, 1 is voided. |
| voidReason | varchar(255) | Reason for voiding the location. |
| dateVoided | datetime | The date and time when the location was voided. |
| voidedBy | int(11) | The userId of the person who voided the location. |
| postalCode | varchar(255) | The postal code for the location. |
| stateProvince | varchar(255) | The state or province where the location is situated. |
| createdByUserId | int(11) | The userId of the person who created the location. |
| lastEditedByUserId | int(11) | The userId of the person who last edited the location. |
Indexes
- Primary Key Index:
- locationId: Ensures that each location has a unique identifier.
- Unique Key on name:
- Ensures that the name of the location is unique.
- Index on locationType:
- Optimizes queries filtering or sorting by locationType.
- Index on lastEditedByUserId:
- Speeds up queries related to who last edited the location.
- Index on createdByUserId:
- Optimizes queries related to who created the location.
- Index on parentLocation:
- Facilitates quick lookups of sub-locations based on their parent location.
Foreign Key Relationships
- locationType
- References the locationTypeId column in the locationtype table.
- Ensures that each location is categorized under a valid location type.
- createdByUserId
- References the mappedId column in the user table.
- Associates the location with the user who created it.
- lastEditedByUserId
- References the mappedId column in the user table.
- Links the location to the user who last edited it.
- parentLocation
- References the locationId column in the location table.
- Allows for a hierarchical structure where a location can have a parent location.
Usage Notes
- This table stores information about geographical locations and their hierarchical relationships. Locations could be cities, villages, districts, or even smaller units like buildings or offices.
- The Primary Key ensures that each location has a unique identifier (locationId), and the Unique Key on name ensures that location names are unique.
- Locations can have sub-locations, which is managed by the parentLocation column. This supports hierarchical structures (e.g., a district containing several cities).
- The voided column helps manage inactive or deleted locations, with corresponding details in the voidReason and dateVoided columns.
- The foreign keys enforce referential integrity:
- locationType ensures that the location type is valid.
- createdByUserId and lastEditedByUserId ensure proper tracking of user actions.
- parentLocation supports the hierarchical relationship within the same table, allowing nested locations.
- The indexes are designed to optimize queries based on location attributes such as location type, user actions, and parent-child location relationships, enabling fast lookups for administrative and operational purposes.